package jp.co.showbiz.common.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class JdbcCsvFile extends HttpServlet {
	
	private static final long serialVersionUID = 3624395271601910006L;
	
	Connection conn = null;

//	String url = "jdbc:mysql://localhost:3306/";
//	String dbName = "showbiz";
//	String driver = "com.mysql.jdbc.Driver";
//	String username = "thinkm";
//	String password = "m";
	
	String url = "jdbc:mysql://192.168.1.50:3306/";
	String dbName = "DevShowbiz?characterEncoding=utf-8";
	String driver = "com.mysql.jdbc.Driver";
	String username = "ais00";
	String password = "ais4321";
	String filePath = "c://csv/";
	Statement stmt;

	public void getDownload(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String fileName = filePath + "downloadLog" + getToday() + ".csv";
		try{
			chkFile(filePath, fileName);
//			PrintWriter out = response.getWriter();
			OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream(fileName),"UTF-8");
			//FileWriter fw = new FileWriter(fileName);
			fw.append("LOG_ID");
			fw.append(',');
			fw.append("UPD_DATE");
			fw.append(',');
			fw.append("TALENT_KANA");
			fw.append(',');
			fw.append("CONTENT_KUBUN");
			fw.append(',');
			fw.append("UID");
			fw.append(',');
			fw.append("TANMATSU_JOUHOU");
			fw.append('\n');
			
			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection(url + dbName, username, password);
			String query = "SELECT * FROM T_LOG_DOWNLOAD";
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			
			while(rs.next()){
				fw.append(rs.getString(1));
				fw.append(',');
				fw.append(rs.getString(2));
				fw.append(',');
				fw.append(rs.getString(3));
				fw.append(',');
				fw.append(rs.getString(4));
				fw.append(',');
				fw.append(rs.getString(5));
				fw.append(',');
				fw.append(rs.getString(6));
				fw.append('\n');
			}
			fw.flush();
			fw.close();
			conn.close();
			
		} catch(Exception e){
			e.printStackTrace();
		}
	}

	public void getAccess(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String fileName = filePath + "accessLog" + getToday() + ".csv";
		try{
			chkFile(filePath, fileName);
//			PrintWriter out = response.getWriter();
			FileWriter fw = new FileWriter(fileName);
			fw.append("LOG_ID");
			fw.append(',');
			fw.append("USER_ID");
			fw.append(',');
			fw.append("PAGE_NAME");
			fw.append(',');
			fw.append("UPD_DATE");
			fw.append('\n');
			
			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection(url + dbName, username, password);
			String query = "SELECT * FROM T_LOG_ACCESS";
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			
			while(rs.next()){
//				fw.append(new String(rs.getString(1).getBytes(), "UTF-8"));
				fw.append(rs.getString(1));
				fw.append(',');
				fw.append(rs.getString(2));
				fw.append(',');
				fw.append(rs.getString(3));
				fw.append(',');
				fw.append(rs.getString(4));
				fw.append('\n');
			}
			fw.flush();
			fw.close();
			conn.close();
			
		} catch(Exception e){
			e.printStackTrace();
		}
	}

	// GET TODAY
	private String getToday(){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		Date today = new Date();
		String date = sdf.format(today);
		
		return date;
	}
	
	public void chkFile(String path, String name){
		// FOLDER CREATE
		File newFile = new File(path);
		if (!newFile.exists()) {
			newFile.mkdirs();
    	}
		// ファイルが存在する場合、前のファイル削除
		File destfile = new File(new File(path), name);
		if(destfile.exists()) {
			destfile.delete();
		}
	}
}
